﻿using Comm;
using Kinlo.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kinlo.Repo
{
    public class AfterWeightFirstRep
    {
        string strConn;
        //Database DB;
        public AfterWeightFirstRep()
        { 
            strConn = System.Configuration.ConfigurationManager.ConnectionStrings["WeightDB"].ConnectionString;
        } 
        public List<AfterWeightFirst> GetAfterFirst(string time1, string time2, string code, string okngStatus)
        {
            try
            {
                if (string.IsNullOrEmpty(code))
                {
                    code = "%";
                }
                else
                {
                    code = "%" + code + "%";
                }

                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"SELECT  *  FROM dbo.AfterWeight_First
                        WHERE ProductDate BETWEEN '{0}' AND '{1}' AND (Code LIKE '{2}' or Code is null)  AND Result_One LIKE '{3}' ORDER BY ProductDate DESC ",
                               time1, time2, code, okngStatus);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();

                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }
        public int DeleteAfterWeight(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"DELETE dbo.AfterWeight_First WHERE Code='{0}'", w.Code);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "DeleteAfterWeight", ex);
                return 0;
            }
        }

        public int Add2(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = @"INSERT dbo.AfterWeight_First
                                ( ProductDate ,Code ,TrayNum ,TrayIndex ,TrayCol ,BeforWeight_One ,
                                  AfterWeight_One ,InjectionNum_One ,InjectionNum_Offset_One ,ISSuppled_One ,
                                  SupplementNum_One ,SupplementWeight_One ,Result_One ,RangeOfInjection_One,IsValidate,[Index],BatchNo,
                                Vacaumm_Test_ok,BeforeWeight_ok,BillNo,ClassTeam,Classes,EquNo,EmpNo)
                        VALUES  ( @ProductDate ,@Code ,@TrayNum ,@TrayIndex ,@TrayCol ,@BeforWeight_One ,
                                  @AfterWeight_One ,@InjectionNum_One ,@InjectionNum_Offset_One ,@ISSuppled_One ,
                                  @SupplementNum_One ,@SupplementWeight_One ,@Result_One ,@RangeOfInjection_One,@IsValidate,@Index,@BatchNo,
                                @Vacaumm_Test_ok,@BeforeWeight_ok,@BillNo,@ClassTeam,@Classes,@EquNo,@EmpNo)";
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Code", w.Code == null ? DBNull.Value : (object)w.Code));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayNum", w.TrayNum == null ? DBNull.Value : (object)w.TrayNum));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayIndex", w.TrayIndex == null ? DBNull.Value : (object)w.TrayIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayCol", w.TrayCol == null ? DBNull.Value : (object)w.TrayCol));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforWeight_One", w.BeforWeight_One == null ? DBNull.Value : (object)w.BeforWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@AfterWeight_One", w.AfterWeight_One == null ? DBNull.Value : (object)w.AfterWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_One", w.InjectionNum_One == null ? DBNull.Value : (object)w.InjectionNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_Offset_One", w.InjectionNum_Offset_One == null ? DBNull.Value : (object)w.InjectionNum_Offset_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@ISSuppled_One", w.ISSuppled_One == null ? DBNull.Value : (object)w.ISSuppled_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementNum_One", w.SupplementNum_One == null ? DBNull.Value : (object)w.SupplementNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementWeight_One", w.SupplementWeight_One == null ? DBNull.Value : (object)w.SupplementWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@Result_One", w.Result_One == null ? DBNull.Value : (object)w.Result_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@RangeOfInjection_One", w.RangeOfInjection_One == null ? DBNull.Value : (object)w.RangeOfInjection_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@IsValidate", w.IsValidate == null ? DBNull.Value : (object)w.IsValidate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    sqlCmd.Parameters.Add(new SqlParameter("@BatchNo", w.BatchNo == null ? DBNull.Value : (object)w.BatchNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@Vacaumm_Test_ok", w.Vacaumm_Test_ok == null ? DBNull.Value : (object)w.Vacaumm_Test_ok));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforeWeight_ok", w.BeforeWeight_ok == null ? DBNull.Value : (object)w.BeforeWeight_ok));
                    sqlCmd.Parameters.Add(new SqlParameter("@BillNo", w.BillNo == null ? DBNull.Value : (object)w.BillNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@ClassTeam", w.ClassTeam == null ? DBNull.Value : (object)w.ClassTeam));
                    sqlCmd.Parameters.Add(new SqlParameter("@Classes", w.Classes == null ? DBNull.Value : (object)w.Classes));
                    sqlCmd.Parameters.Add(new SqlParameter("@EquNo", w.EquNo == null ? DBNull.Value : (object)w.EquNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@EmpNo", w.EmpNo == null ? DBNull.Value : (object)w.EmpNo));

                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "Add2", ex);
                return 0;
            } 
        }

        public IList<AfterWeightFirst> GetAfterFirst1()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = @"SELECT TOP 1000 * FROM dbo.AfterWeight_First WHERE IsValidate=1 AND	ProductDate>DATEADD(DAY,-7,GETDATE()) ORDER BY ProductDate DESC";
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var AfterWeightFirsts = sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>(); 
                    if (AfterWeightFirsts.Count > 0)
                        return AfterWeightFirsts;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst1", ex);
                return null;
            }
        }

        public DataTable GetDataBase()
        {
            try
            { 
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = @"SELECT REPLACE(CONVERT(NVARCHAR(19),T.ProductDate,121),'-','/') AS ProductDate  ,
                   T.[1] ,T.[2] ,T.[3] ,T.[4] ,T.[5] ,T.[6] ,T.[7] ,T.[8] ,T.[9] , T.[10] , T.[11] , T.[12]
                    from dbo.AfterWeight_First as C 
                    PIVOT(MIN(InjectionNum_One) FOR [TrayCol] IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS T";
                    SqlDataAdapter da = new SqlDataAdapter(cmd, conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt); 
                    return dt;
                } 
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetDataBase", ex);
                return null;
            }
        }

        public List<UnExportDate>GetUnExportDates()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = @"SELECT DISTINCT(CONVERT(NVARCHAR(10),ProductDate,121)) Date FROM dbo.AfterWeight_First WHERE (IsExport=0 OR IsExport IS NULL)
                    AND ( ProductDate< CONVERT(NVARCHAR(10),GETDATE(),121)) ORDER BY Date  ";
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var unExports = sqlCmd.ExecuteReader().ReaderToList<UnExportDate>();
                    if (unExports.Count > 0)
                        return unExports;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetUnExportDates", ex);
                return null;
            } 
        }

        public List<AfterWeightFirst> GetAfterFirst(string date)
        {
            try
            { 
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"SELECT * FROM dbo.AfterWeight_First WHERE  CONVERT(NVARCHAR(10),ProductDate,121)='{0}'", date);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }

        public int UpdateExportDataStatus(string date)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"UPDATE dbo.AfterWeight_First SET IsExport=1 WHERE  CONVERT(NVARCHAR(10),ProductDate,121)='{0}'", date);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex);
                return 0;
            }
        }

        public List<AfterWeightFirst> GetAfterFirstByTime(DateTime date1, DateTime date2)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@" SELECT * FROM AfterWeight_First WHERE (IsExport=0 or IsExport is NULL) AND ProductDate BETWEEN '{0}' and '{1}'",
                        date1.ToString("yyyy-MM-dd HH:mm:ss"), date2.ToString("yyyy-MM-dd HH:mm:ss"));
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex);
                return null;
            }
        }
        public List<string> GetAfterFirstUnExportDate()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@" SELECT DISTINCT CONVERT(NVARCHAR(10),ProductDate,121) DD  FROM AfterWeight_First WHERE (IsExport=0 or IsExport is NULL) ORDER BY DD DESC");
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    var strs = new List<string>();
                    while (reader.Read())
                    {
                        var s = reader[0].ToString();
                        strs.Add(s); ;
                    }
                    return strs;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "GetAfteGetAfterFirstUnExportDaterFirst", ex);
                return null;
            }
        }
        public int UpdateExportDataStatus(Guid? id)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"UPDATE  AfterWeight_First SET IsExport=1 WHERE ID='{0}'  ", id);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex);
                return 0;
            }
        }
        public int UpdateExportDataStatus(DateTime date1, DateTime date2)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@" UPDATE dbo.AfterWeight_First SET IsExport=1 WHERE (IsExport=0 or IsExport is NULL) AND ProductDate BETWEEN '{0}' and '{1}'",
                           date1.ToString("yyyy-MM-dd HH:mm:ss"), date2.ToString("yyyy-MM-dd HH:mm:ss"));
                    //var cmd = string.Format(@"UPDATE  AfterWeight_First SET IsExport=1 WHERE ID='{0}'  ", id);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex);
                return 0;
            }
        }
    }

}
